
package com.be.beadmin.project.repository;

import com.be.beadmin.project.entity.TeamInfo;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.criteria.CriteriaBuilder;
import java.util.List;

/**
* @author zjm
* @date 2023-02-02
**/
public interface TeamInfoRepository extends JpaRepository<TeamInfo, String>, JpaSpecificationExecutor<TeamInfo> {

    List<TeamInfo> findTeamInfoByTeamIdAndUserIdAndCategoryAndDelete(String teamId,String userId,String category,Integer delete);

    TeamInfo findTeamInfoByIdAndDelete(String id, Integer delete);

    List<TeamInfo> findTeamInfosByTeamIdAndDelete(String teamId, Integer delete);


    TeamInfo findTeamInfoById(String id);

    List<TeamInfo> findTeamInfoByTeamIdAndCategoryInAndDelete(String teamId, List<String> category, Integer delete);

    @Modifying
    @Transactional
    @Query(value = "update t_pm_team_info ti set ti.is_delete = ?1 where ti.team_id = ?2 and ti.is_delete = ?3",nativeQuery = true)
    void deleteTeamInfoByInfoIdAndDelete(int deleted,String teamId,int nodelete);

    @Modifying
    @Transactional
    void deleteTeamInfosByTeamIdAndRoleName(String teamId, String roleName);
    TeamInfo findTeamInfoByTeamIdAndCategoryAndDelete(String teamId, String category, int delete);

    //角色类别下有多个
    List<TeamInfo> findTeamInfoByCategoryAndTeamIdAndAndDelete(String category, String teamId, int delete);

    @Modifying
    @Transactional
    @Query(value = "select distinct ti.team_id from t_pm_team_info ti where ti.user_id = ?1 and ti.is_delete = ?2",nativeQuery = true)
    List<String> findUniqueTeamIdByUserIdAndDelete(String userId, int delete);

    List<TeamInfo> findTeamInfosByTeamIdAndCategoryAndDelete(String teamId, String category, Integer delete);

    List<TeamInfo> findTeamInfoByTeamIdAndDelete(String teamId, Integer delete);


    @Query(value = "select sr.name from(SELECT\n" +
            "\tddt.key_name\n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT DISTINCT\n" +
            "\t\tr.`code` \n" +
            "\tFROM\n" +
            "\t\t(\n" +
            "\t\tSELECT\n" +
            "\t\t\tur.* \n" +
            "\t\tFROM\n" +
            "\t\t\t(\n" +
            "\t\t\tSELECT\n" +
            "\t\t\t\tti.user_id \n" +
            "\t\t\tFROM\n" +
            "\t\t\t\tt_pm_team_info ti\n" +
            "\t\t\t\tLEFT JOIN sys_user u ON ti.user_id = u.id \n" +
            "\t\t\tWHERE\n" +
            "\t\t\t\tti.team_id = '8a827f828c677b49018c6c39e2490a9b' \n" +
            "\t\t\t\tAND ti.is_delete = 0 \n" +
            "\t\t\t\tAND u.is_delete = 0 \n" +
            "\t\t\t) AS tu\n" +
            "\t\t\tLEFT JOIN sys_user_role ur ON tu.user_id = ur.user_id \n" +
            "\t\t) AS tuu\n" +
            "\t\tLEFT JOIN sys_role r ON tuu.role_id = r.id \n" +
            "\t) AS tr\n" +
            "\tRIGHT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tdd.key_name \n" +
            "\tFROM\n" +
            "\t\tsys_dict_data dd\n" +
            "\t\tLEFT JOIN sys_dict_type dt ON dd.dict_type_id = dt.id \n" +
            "\tWHERE\n" +
            "\t\tdt.dict_name = '有效团队角色' \n" +
            "\t\tAND dd.is_delete = 0 \n" +
            "\t\tAND dt.is_delete = 0 \n" +
            "\t) AS ddt ON tr.`code` = ddt.key_name \n" +
            "WHERE\n" +
            "\ttr.`code` IS NULL) as res left join sys_role sr on res.key_name = sr.`code`", nativeQuery = true)
    List<String> validTeamRole(String teamId);

    List<TeamInfo> findTeamInfoByTeamIdAndRoleNameAndUserIdAndDelete(String teamId,String roleName,String userId, Integer delete);
    @Modifying
    @Transactional
    @Query(value = "select ti.user_name from t_pm_team_info ti where ti.team_id = ?1 and ti.category = ?2 and ti.is_delete = ?3",nativeQuery = true)
    List<String> findNameByTeamIdAndCategoryAndDelete(String teamId, String category, Integer delete);

    @Modifying
    @Transactional
    void deleteTeamInfoByTeamIdAndUserIdAndRoleId(String teamId, String userId, String roleId);


    List<TeamInfo> findTeamInfoByTeamIdAndRoleNameAndDelete(String teamId,String roleName, Integer delete);

    @Query(value = "SELECT\n" +
            "\tcount(ti.category)\n" +
            "FROM\n" +
            "\tt_pm_team_info ti\n" +
            "\tLEFT JOIN sys_user u ON ti.user_id = u.id \n" +
            "WHERE\n" +
            "\tti.team_id = ?1 \n" +
            "\tAND ti.is_delete = 0 \n" +
            "\tAND u.is_delete = 0 \n" +
            "\tAND (ti.category = '项目经理' or ti.category ='生产经理' or ti.category = '技术经理') \n" +
            "GROUP BY\n" +
            "\tti.category \n" +
            "HAVING\n" +
            "\tcount( ti.category ) > 1",nativeQuery = true)
    List<Integer> isOnlyOneManager(String teamId);

}